<?php
/*
 * Created on Apr 5, 2006
 *
 */
 
require_once('sql_connection.php');
require_once('config.php');
 
class Sql_discussion extends Sql_connection {

    function __construct($mysql_link = null) {
        parent :: __construct($mysql_link);
    }

    function get_threads($amount = null, $offender = null) {
        assert(is_null($amount) || is_numeric($amount));
        assert(is_null($offender) || is_numeric($offender));

        $query = "SELECT id, title, date, time, age ";
        
        if ($offender) {
            $query .= "FROM Discussion_threads, Offender_discussion ";
            $query .= "WHERE Offender_discussion.discussion = Discussion_threads.id 
                AND Offender_discussion.offender = $offender ";
        }
        else { 
            $query .= "FROM Discussion_threads ";
        }
            
        $query .= "ORDER BY age DESC";

        if ($amount)
            $query .= " LIMIT 0, $amount";

        return get_results($query);
    }


    public function thread_with_posts($thread) {
        $thread = $this->get_thread($thread);
        $posts  = $this->_get_posts($thread['id']);


        #add posts to thread
        foreach($posts as $post) {
            $thread['reply'][] = $post;
        }

        $thread['postcount'] = $this->_postcount($thread['id']);

        return $thread;


    }

    /**
     * Inserts a new thread into Discussion_threads
     */
    function insert_thread($title) {
        htmlspecialchars(mysql_input($title));

        $age = time();
        $query = "INSERT INTO Discussion_threads(date, time, title, age)
            VALUES(now(), now(), '$title', '$age')";

        $result = mysql_query($query);
        if (!$result)
            die("Insert thread - DB error: " . mysql_error());

        return mysql_insert_id();

    }

    /**
     * Inserts a post into Discussion_post.
     */
    function insert_post($thread, $text, $sage, $poster_name) {
        assert(is_numeric($thread));
        assert($this->thread_exists($thread));

        if (!$sage) {
            $this->_age($thread);
        }

        $query = sprintf("INSERT INTO Discussion_posts (
            date, time, user, ip, thread, text)

                VALUES(NOW(), NOW(), '%s', '%s', '%s', '%s')",
                mysql_input($poster_name),
                $_SERVER['REMOTE_ADDR'],
                $thread,
                mysql_input($text));

        $result = mysql_query($query);
        if (!$result)
            die ("Insert thread - DB error: " . mysql_error());

        return mysql_insert_id();
    }

    /**
     * Function to check if a specified thread exists.
     */
    function thread_exists($id) {
        if (!is_numeric($id))
            die ("thread_exists() - $id not numeric");

        $query = "SELECT id FROM Discussion_threads";

        return ( mysql_num_rows(
                    mysql_query($query)) > 0 );
    }

    function simple_overview($count = null, $offset = null) {

        if (!$count) {
            global $CONFIG;
            $count = $CONFIG['discussion_shown'];
        }

        if (!is_numeric($count))
            die ("simple_overview() - count not numeric");



        $query = "SELECT DISTINCT Discussion_threads.id, Discussion_threads.title
            FROM Discussion_threads, Discussion_posts
            WHERE Discussion_posts.thread = Discussion_threads.id
            ORDER BY Discussion_threads.age DESC";
#TODO: replace with get_threads?
        if (is_numeric($offset)) {
            $query .= " LIMIT $offset, $count";

        }
        else {
            $query .= " LIMIT 0, $count";
        }


#add postcount
        $threads = get_results($query);
        for ($i = 0; $i < count($threads); $i++) {
            $id = $threads[$i]['id'];
            $threads[$i]['postcount'] = $this->_postcount($id);
        }

        return $threads;
    }

    function detailed_overview($count = null) {
        if (!$count) {
            global $CONFIG;
            $count = $CONFIG['discussion_shown'];
        }
        if (!is_numeric($count))
            die ("detailed_overview() - $count not numeric");


        $threads = $this->get_threads($count);
        $overview = array();
        foreach($threads as $thread) {
            $id = $thread['id'];
            $posts = $this->_get_posts($id, 2);

            $thread['first_post'] = $posts[0];
            if ($posts[0]['id'] != $posts[1]['id']) {
# Only one post in the thread.
                $thread['last_post' ] = $posts[1];
            }
            $thread['postcount'] = $this->_postcount($id);

            $overview[] = $thread;
        }

        return $overview;
    } 

    public function get_thread($id) {
        if (!is_numeric($id)) {
            die ("get thread - id is not numeric");
        }

        $query = "SELECT id, title, date, time, age
            FROM Discussion_threads
            WHERE id = '$id'";

        return get_single_result($query);	
    }

    /**
     * "Ages" a thread. Updates the age field in Discussion_threads
     * to current unix time. Used to sort threads by last (aging) post.
     */
    public function _age($thread) {
        if (!is_numeric($thread))
            die ("age_thread - invalid datatype for $thread");

        $time = time();
        $query = "UPDATE Discussion_threads
            SET age = '$time'
            WHERE id = '$thread'";

        if (!mysql_query($query))
            die ("age_thread - DB error: " . mysql_error());
    }

    private function _get_posts($thread, $omit = null) {
        if (!is_numeric($thread))
            die ("get posts - invalid datatype for $thread");

        //to check if omit is needed, find postcount
        if ($omit) {
            $query = "SELECT count(id) FROM Discussion_posts WHERE thread = '$thread'";

            $postcount = get_single_result($query);



            if ($postcount > $omit) {

                //TODO: Get a real mysql query, to get rid of all this logic.
                if (!is_numeric($omit))
                    die ("get posts - invalid datatype of $omit");

                // Get the first post
                $query = "SELECT id, date, time, user, text	
                    FROM Discussion_posts
                    WHERE thread = '$thread'
                    ORDER BY date ASC, time ASC
                    LIMIT 1";

                $result = mysql_query($query);
                if (!$result)
                    die ("get post - DB error " . mysql_error);
                $first_post = mysql_fetch_assoc($result);

                //Get the $omitt latests posts
                $omit = $omit - 1;
                $query = "SELECT id, date, time, user, text	
                    FROM Discussion_posts
                    WHERE thread = '$thread'
                    ORDER BY date DESC, time DESC
                    LIMIT 0, $omit";

                $latest_posts = get_results($query);
                //put in right order
                $latest_posts = array_reverse($latest_posts);

                //merge the two, and return
                return array_merge(Array($first_post), $latest_posts);

            }
        }
        //getting all the posts
        $query = "SELECT id, date, time, user, text	
            FROM Discussion_posts
            WHERE thread = '$thread'
            ORDER BY date, time ASC";
        return get_results($query);




    }

    /**
     * Returns a given threads reply count
     */
    private function _postcount($thread) {
        if (!is_numeric($thread)) {
            die( "_postcount - id not numeric." );
        }
        $query = "SELECT COUNT(id)
            FROM Discussion_posts
            WHERE thread = '$thread'";
        $result = get_single_result($query);
        return $result[0];
    }
}

?>
